﻿/* 
 * Change log
 * 1.0.0, 19 MAY 2014 - Initial release.
 * 
 * 1.0.1, 21 MAY 2014
 * - Fixed bugs in internal query method affecting folder-specific queries
 * 
 * 1.0.2, 24 MAY 2014
 * - Fixed bug in UpdatePathLocator method that was preventing movement of files and folders within the hierarchy
 * 
 * 1.0.3, 25 MAY 2014
 * - Reimplemented Update method and updated the other change methods and Submit() to conduct all changes as a 
 *   batch operation.  This is more consistent with WCF RIA Services DomainService base class methodology.
 *   
 * 1.0.4, 30 MAY 2014
 * - Updated Delete command to run in batch mode (Insert and Update commands already ran that way)
 * - Changed Update command to silently fail if an attempt is made to change something unchangeable (e.g., IsFolder)
 * 
 * 1.0.5, 29 SEP 2014
 * - Fixed bug that was preventing GetNodeByID() queries from retrieving non-root nodes
 * 
 * 1.0.6, 29 SEP 2014
 * - Fixed bug in GetFolders() query introduced in 1.0.5 (same day)
 * 
 */

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.ServiceModel.DomainServices.Server;

using System.Web.Configuration;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Types;

namespace SqlFileTableExtension.DataSources
{
    [Description("Please provide a connection string for the SQL Server database containing the FileTable.")]
    public class SqlFileTableDataSource : DomainService
    {
        #region private members and initialization

        /// <summary>
        /// The connection to the SQL Server database containing the FileTable
        /// </summary>
        private System.Data.SqlClient.SqlConnection _conn;

        /// <summary>
        /// The name of the FileTable
        /// </summary>
        private string _fileTableName;

        public override void Initialize(DomainServiceContext context)
        {
            base.Initialize(context);

            // setup the connection
            string connString = WebConfigurationManager.ConnectionStrings[(this.GetType().FullName)].ConnectionString;

            if (connString == null || connString.Length == 0)
            {
                throw new Exception("Unable to retrieve connection string from web.config in the connection strings section under "
                  + typeof(SqlFileTableDataSource).FullName);
            }
            else 
            {
                _conn = new SqlConnection(connString);

                // discover and record the FileTable name
                string strSql = "SELECT * FROM sys.tables WHERE filestream_data_space_id IS NOT NULL;";
                SqlCommand cmd = new SqlCommand(strSql, _conn);
                _conn.Open();
                SqlDataReader result = cmd.ExecuteReader();

                List<string> fileTableNames = new List<string> { };

                while (result.Read())
                {
                    fileTableNames.Add((string)result["name"]);
                }

                // while this may return more than one FileTable - I haven't accounted for that possibility just yet.
                if (fileTableNames.Count == 0)
                {
                    throw new Exception("Unable to locate any FileTables in the specified SQL Server database (is the FileTable set up yet?)");
                }
                else
                {
                    _fileTableName = "[" + fileTableNames[0] + "]";
                }

                result.Close();
                _conn.Close();
            }
        }

        #endregion

        #region Database internal methods
        // These methods accept and execute T-SQL syntax directly.
        // Any SQL statements passed to these methods are run against
        // the database directly.  That's why they are private.
        // YOU HAVE BEEN WARNED; don't accept an unchecked string here.

        /// <summary>
        /// Internal method to make user input strings compatible with direct use in a T-SQL query
        /// </summary>
        /// <param name="input">The user's input string</param>
        /// <returns>A string that can be safely appended to a query string</returns>
        private string Safe(string input)
        {
            //TODO: Return to this method and check for other forms of potential SQL injection
            // for the moment, leaving unsafe string handling to the Web application; this method simply
            // escapes the use of a single quotation so as not to terminate a string if a user either
            // accidentally or intentionally includes a single quotation mark in an input string
            return input.Replace("'", "''");
        }

        /// <summary>
        /// Internal method to query the FileTable and return them as a collection of FileNode objects.
        /// </summary>
        /// <remarks>
        /// This method is called internally by the Query methods exposed as Services.  All non-default
        /// parameters are logically combined (Boolean AND) in the resulting SQL query.
        /// </remarks>
        /// <param name="parentPathLocator">If set, returns only Files and Folders underneath the given ParentPathLocator</param>
        /// <param name="includeDescendants">If true, returns everything below the specified ParentPathLocator (default = true)</param>
        /// <param name="includeFileContents">If true, also includes the content of any files returned (default = false)</param>
        /// <param name="nameFilter">If set to a non zero length string, returns only file names matching this string</param>
        /// <param name="IDs">If set, searches only within the specific files or folders with the given stream_id values</param>
        /// <param name="contains">If set, conducts full text CONTAINS query using this expression, and orders results by rank</param>
        /// <param name="limitResults">If set to a number other than zero, returns no more than the specific number of results</param>
        /// <param name="getFoldersOnly">If true, returns only FileNodes representing Folders</param>
        /// <returns>IQueryable collection of FileMeta objects (or NULL if no results are returned)</returns>
        private List<FileNode> QueryNodes(
            string parentPathLocator = null,    // by default, start at the root of the FileTable
            bool includeDescendants = true,     // by default, include contents of all subfolders
            bool includeFileContents = false,   // by default, don't include file contents (just build out FileNodes)
            string nameFilter = null,           // by default, don't search for a particular file name pattern
            List<string> IDs = null,            // by default, don't retrieve only these specific files or folders
            string contains = null,             // by default, do not perform full text CONTAINS query
            int limitResults = 0,               // by default, do not limit the number of results
            bool getFoldersOnly = false)        // by default, retrieve FileNodes representing both Files and Folders
        {
            // build the query string by hand
            // Note - this is a decidedly OLD SCHOOL approach - e.g., avoiding Entity Framework, linq, everything
            // this was a choice, in order to avoid problems with handling hierarchyid fields in the file table
            // with frameworks that write your SQL for you (but do not understand this vendor-specific field type).
            // Since the schema of file tables is fixed, this approach will work with any file table.  
            StringBuilder strSql = new StringBuilder("SELECT");

            // if the result set was limited, and we are NOT doing a full text query, use the TOP keyword here
            if ((contains == null || contains.Length == 0) && limitResults > 0)
            {
                strSql.Append(" TOP " + limitResults);
            }

            // append core columns that we will always retrieve for every query
            strSql.Append(" stream_id, name, file_type, cached_file_size, is_directory, " +
                "file_stream.GetFileNamespacePath() AS path, " + 
                "file_stream.GetFileNamespacePath(1, 0) AS fullpath, " +
                "path_locator.ToString() as path_locator_string, " + 
                "parent_path_locator.ToString() as parent_path_locator_string");
            
            // also request file contents from the database if user wanted them
            if (includeFileContents) { strSql.Append(", file_stream"); }

            // also request RANK if the user wanted us to do a full text CONTAINS query
            if (contains != null && contains.Length > 0) { strSql.Append(", KEY_TBL.RANK"); }

            // begin the FROM clause
            strSql.Append(" FROM " + _fileTableName);

            // If the query included the contains parameter, then create a CONTAINSTABLE and join to it here,
            // so that we can order the results by search rank (note - we don't expose the RANK to the user).
            if (contains != null && contains.Length > 0)
            {
                strSql.Append(" INNER JOIN CONTAINSTABLE (");
                strSql.Append(_fileTableName);
                strSql.Append(", file_stream, '");
                strSql.Append(Safe(contains));      // again, NOT VALIDATING, just preventing SQL injection
                strSql.Append("'");

                // if limiting result set in the context of a contains query, do so here
                if (limitResults > 0) { strSql.Append(", " + limitResults.ToString());  }

                strSql.Append(") AS KEY_TBL ON ");
                strSql.Append(_fileTableName);
                strSql.Append(".stream_id = KEY_TBL.[KEY]");
            }

            if ((parentPathLocator != null && parentPathLocator.Length > 0) ||         // (a parent path is specified, OR
                ((parentPathLocator == null || parentPathLocator.Length == 0) &&       // (no parent path is specified, AND
                !includeDescendants &&                                                 // query doesn't include descendants, AND  
                (IDs == null || IDs.Count == 0)) ||                                    // query is not a query for a specific ID)) OR
                (nameFilter != null && nameFilter.Length > 0) ||                       // query includes name filter, OR
                (IDs != null && IDs.Count > 0) ||                                      // specific items were requested by ID, OR
                (getFoldersOnly)                                                       // limiting output to folders only
            )
            {
                // start the WHERE clause; we apparently need it (one of the above conditions triggered this)
                strSql.Append(" WHERE (");

                // keep track of which items have been injected into the WHERE clause
                bool _setFoldersOnly = false;   // true if we requested only FileNodes representing Folders
                bool _setPath = false;          // true if we set a specific query path
                bool _setName = false;          // true if we set a name filter
                //bool _setID = false;            // true if we request a specific ID

                // if appropriate, specify that the query returns only Folders
                if (getFoldersOnly) 
                { 
                    strSql.Append("is_directory = 1");
                    _setFoldersOnly = true;
                }

                // specify the path for the query
                if ((parentPathLocator == null || parentPathLocator.Length == 0) && 
                    !includeDescendants &&
                    (IDs == null || IDs.Count == 0))
                {
                    // query includes ONLY files and folders at the root of the hierarchy
                    // and is not requesting a specific node or nodes by ID
                    if (_setFoldersOnly) { strSql.Append(" AND"); }
                    strSql.Append(" parent_path_locator IS NULL");
                    _setPath = true;
                }
                else if ((parentPathLocator != null && parentPathLocator.Length > 0) && !includeDescendants)
                {
                    // query includes ONLY the specified folder
                    if (_setFoldersOnly) { strSql.Append(" AND"); }
                    strSql.Append(" parent_path_locator.ToString() = '" + Safe(parentPathLocator) + "'");
                    _setPath = true;
                } 
                else if ((parentPathLocator != null && parentPathLocator.Length > 0) && includeDescendants)
                {
                    // query includes the specified folder and all of its descendants
                    // note - database should implicitly call Parse against the string representation of the HierarchyId
                    if (_setFoldersOnly) { strSql.Append(" AND"); }
                    strSql.Append(" path_locator.IsDescendantOf('" + Safe(parentPathLocator) + "') = 1");
                    _setPath = true;
                };

                // filter by name, if specified
                if (nameFilter != null && nameFilter.Length > 0)
                {
                    if (_setFoldersOnly || _setPath) { strSql.Append(" AND"); }
                    strSql.Append(" name LIKE '%");
                    strSql.Append(Safe(nameFilter));
                    strSql.Append("%'");
                };

                // retrieve a specific ID, if requested
                if (IDs != null && IDs.Count > 0)
                {
                    if (_setFoldersOnly || _setPath || _setName) { strSql.Append(" AND"); }
                    strSql.Append(" stream_id");
                    if (IDs.Count == 1)
                    {
                        // only one specific ID was requested
                        strSql.Append(" = '" + Safe(IDs[0]) + "'");
                        //_setID = true;
                    } else if (IDs.Count > 1)
                    {
                        // query includes more than one specific ID
                        strSql.Append(" IN (");
                        bool _pastOne = false;
                        foreach (string s in IDs)
                        {
                            if (_pastOne) { strSql.Append(", "); }
                            strSql.Append("'" + Safe(s) + "'");
                            _pastOne = true;
                        };
                        strSql.Append(")");
                        //_setID = true;
                    }

                }

                // conclude the WHERE clause (we're now done with it)
                strSql.Append(")");
            }

            // order results
            if ((contains != null && contains.Length > 0) && limitResults > 0)
            {
                strSql.Append(" ORDER BY KEY_TBl.RANK DESC");
            } 
            else
            {
                // we're not doing a full text query; order by the path in which
                // the file or folder exists
                strSql.Append(" ORDER BY path ASC");
            }

            // conclude query
            strSql.Append(";");

            // Output the query if in DEBUG mode
            System.Diagnostics.Debug.WriteLine("Executing this query: " + strSql.ToString());

            // run the query against the connection
            SqlCommand cmd = new SqlCommand(strSql.ToString(), _conn);
            _conn.Open();
            SqlDataReader result = cmd.ExecuteReader();

            // enumerate results and build an IQueryable connection of FileMeta objects to contain them
            List<FileNode> theFiles = new List<FileNode> { };
            FileNode theFile;
            while (result.Read())
            {
                theFile = new FileNode();
                theFile.ID = (Guid)result["stream_id"];
                theFile.Name = (string)result["name"];
                if (result["file_type"] != DBNull.Value) { theFile.FileType = (string)result["file_type"]; }
                if (result["cached_file_size"] != DBNull.Value) { theFile.Size = (long)result["cached_file_size"]; }
                theFile.Path = (string)result["path"];
                theFile.FullPath = (string)result["fullpath"];
                theFile.IsFolder = (bool)result["is_directory"];
                if (includeFileContents && result["file_stream"] != DBNull.Value) { theFile.Data = (byte[])result["file_stream"]; }
                theFile.PathLocator = (string)result["path_locator_string"];
                if (result["parent_path_locator_string"] != DBNull.Value) { theFile.ParentPathLocator = (string)result["parent_path_locator_string"]; }

                theFiles.Add(theFile);
            }

            // close the DataReader
            result.Close();
            _conn.Close();

            // return the result
            return theFiles;

        }

        #endregion

        #region Submit

        /// <summary>
        /// The list of SqlCommand objects to be executed when Submit() runs.
        /// Each command is populated by one of its calls to Insert, Update, or Delete methods.
        /// </summary>
        private List<SqlCommand> ChangeCommands;

        /// <summary>
        /// Overridden method to invoke domain service methods for all changes in a ChangeSet when a client calls SubmitChanges.
        /// </summary>
        /// <param name="changeSet">The set of changes to be processed.</param>
        /// <returns>True if the batch operation has completed successfully, false otherwise</returns>
        public override bool Submit(ChangeSet changeSet)
        {
            ChangeCommands = new List<SqlCommand> { };
            return base.Submit(changeSet);
        }

        /// <summary>
        /// Overridden method to persist changes to the database after Submit() has processed all changes.
        /// </summary>
        /// <returns>True if changes are successfully persisted; false if changes are not successfully persisted</returns>
        protected override bool PersistChangeSet()
        {
 	        try
            {
                _conn.Open();
                foreach (SqlCommand cmd in ChangeCommands)
                {
                    cmd.ExecuteNonQuery();
                }
                _conn.Close();
                ChangeCommands = new List<SqlCommand> { };
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Error running an SQL command: " + ex.Message, ex);
            }
        }

        #endregion

        #region Queries

        /// <summary>
        /// Default query - returns meta definition of all nodes in the FileTable
        /// </summary>
        /// <returns>All nodes in the FileTable, but none of the file data</returns>
        [Query(IsDefault = true)]
        public List<FileNode> GetAllNodes()
        {
            // returns all FileNodes and no file contents
            return QueryNodes(null, true);
        }

        /// <summary>
        /// Query to retrieve a single FileNode with the given stream_id from the FileTable.  This query
        /// returns the FileNode definition and the file data (file contents are included).
        /// </summary>
        /// <param name="ID">The Guid of the desired FileNode</param>
        /// <returns>The requested FileNode, or null (if no Guid is given, or FileNode is not found)</returns>
        [Query(IsComposable = false)]
        public FileNode GetNodeByID(Nullable<Guid>ID)
        {
            FileNode theNode = null;

            if (ID.HasValue && (ID != Guid.Empty))
            {
                List<string> theNodeId = new List<string> { };
                theNodeId.Add(ID.ToString());
                List<FileNode> theNodes = QueryNodes(null, false, true, null, theNodeId);
                if (theNodes.Count == 1) { theNode = theNodes[0]; }

            }
            return theNode;
        }

        /// <summary>
        /// Query to retrieve all nodes in the FileTable with a name matching a given pattern.  Does not return
        /// file contents for any matching nodes (call GetNodeByID or SearchForNodes to retrieve file contents).
        /// </summary>
        /// <param name="nodeName">The name for which to search; if null, returns an empty set of FileNodes</param>
        /// <returns>All FileNodes whose name matches the nodeName parameter, without data returned</returns>
        [Query(ResultLimit=1000)]
        public IQueryable<FileNode> GetNodesByName(string nodeName)
        {
            if (nodeName != null && nodeName.Length > 0)
            {
                return QueryNodes(null, true, false, nodeName,null, null, 1000).AsQueryable();
            }
            else
            {
                return new List<FileNode> { }.AsQueryable();
            }
        }

        /// <summary>
        /// Query to retrieve all nodes in a given folder, with or without the contents of subfolders.
        /// Does not return file contents for files in the given folder (call GetNodeByID or SearchForNodes 
        /// to retrieve file contents).
        /// </summary>
        /// <remarks>This query is meant to allow for clients to browse one folder at a time.
        /// 
        /// The PathLocator value to use should be the PathLocator corresponding to the folder that you
        /// want to browse.  This can be obtained from the PathLocator property of the FileNode representing
        /// the folder, or, from the ParentPathLocator property of any file or folder in the target 
        /// folder.</remarks>
        /// <param name="PathLocator">The PathLocator of the folder to retrieve (null = root)</param>
        /// <param name="IncludeSubfolders">If true, retrieves FileNodes from all subfolders; 
        /// if false, retrieves nodes (files and folders) in the PathLocator folder only</param>
        /// <returns>Queryable list of FileNodes matching search parameters</returns>
        [Query()]
        public IQueryable<FileNode> GetNodesByFolder(string PathLocator, Nullable<bool> IncludeSubfolders)
        {
            if (
                (PathLocator == null || PathLocator.Length == 0) &&
                (IncludeSubfolders == null || IncludeSubfolders == false))
            {
                return new List<FileNode> { }.AsQueryable();
            }
            else
            {
                bool _includeSubfolders = false, _includeData = false;

                if (IncludeSubfolders.HasValue) { _includeSubfolders = IncludeSubfolders.Value; }

                return QueryNodes(
                    PathLocator,
                    _includeSubfolders,
                    _includeData).AsQueryable();

            }

        }

        /// <summary>
        /// Query to retrieve all FileNodes representing Folders.  May limited to a specific folder, 
        /// with or without its subfolders. 
        /// </summary>
        /// <param name="PathLocator">The PathLocator of the folder to retrieve (null = root)</param>
        /// <param name="IncludeSubfolders">If true, retrieves FileNodes from all subfolders; 
        /// if false, retrieves nodes (files and folders) in the PathLocator folder only</param>
        /// <returns>Queryable list of FileNodes matching search parameters</returns>
        /// <remarks>This query is meant to be useful in constructing user interfaces to select
        /// folders (for example, when creating a new FileNode and placing it within an existing
        /// folder in the FileTable).
        /// 
        /// Because this query only returns folders, it defaults to returning everything.  If both 
        /// parameters are null, it will return all FileNodes representing all Folders in the FileTable,
        /// with an unlimited number of FileNodes returned.
        /// 
        /// </remarks>
        [Query()]
        public IQueryable<FileNode> GetFolders(string PathLocator, Nullable<bool> IncludeSubfolders)
        {
            bool _includeSubfolders = true;
            if (IncludeSubfolders.HasValue) { _includeSubfolders = IncludeSubfolders.Value; }
            return QueryNodes(PathLocator, _includeSubfolders, false, null, null, null, 0, true).AsQueryable();
        }

        /// <summary>
        /// A fully parameterized query to search FileNodes and, if desired, file contents
        /// </summary>
        /// <param name="StartAtPathLocator">The PathLocator of the folder at which to start the search (null = root)</param>
        /// <param name="IncludeSubfolders">If true, includes all subfolders; if false, only searches the startingPathLocator folder</param>
        /// <param name="IncludeData">If true, returns file data along with search results; if false, returns FileNode definitions only</param>
        /// <param name="Name">If set to a non-zero-length string, limits search results to FileNode names matching this pattern</param>
        /// <param name="Containing">If provided, performs a full text CONTAINS query against the file contents and returns results
        /// in order of search result rank (closest results first); will accept any valid text for the CONTAINS T-SQL syntax</param>
        /// <param name="MaxResults">If greater than zero, returns no more than the specified number of search results (e.g., 10 will 
        /// return the top ten search hits)</param>
        /// <remarks>All parameters have default values; unless otherwise specified, the search:
        /// - will start at the root of the FileTable and include all subfolders
        /// - will return file contents along with matching FileNodes
        /// - will not filter by Name
        /// - will not filter by a specific list of Node IDs
        /// - will return no more than 1,000 results
        /// 
        /// Note, the order of results is arbitrary unless the containing parameter is specified.  This means that, if more than the
        /// desired maximum number of search results exists in the database, the specific FileNodes returned are arbitrary. </remarks>
        /// <returns>A queryable list of FileNodes matching the specified parameters</returns>
        [Query(ResultLimit = 1000)]
        public IQueryable<FileNode> SearchForNodes(
            string StartAtPathLocator,
            Nullable<bool> IncludeSubfolders,
            Nullable<bool> IncludeData,
            string Name,
            string Containing,
            Nullable<int> MaxResults
            )
        {

            // in the event that *all* parameters are null as provided, return nothing
            if (
                (StartAtPathLocator == null || StartAtPathLocator.Length == 0) &&
                (IncludeSubfolders == null || IncludeSubfolders == false) &&
                (IncludeData == null || IncludeData == false) &&
                (Name == null || Name.Length == 0) &&
                (Containing == null || Containing.Length == 0) &&
                (MaxResults == null || MaxResults == 0))
            {
                return new List<FileNode> { }.AsQueryable();
            }
            else
            {
                bool _includeSubfolders = true, _includeData = false;
                int _maxResults = 1000;

                if (IncludeSubfolders.HasValue) { _includeSubfolders = IncludeSubfolders.Value; }
                if (IncludeData.HasValue) { _includeData = IncludeData.Value; }
                if (MaxResults.HasValue) 
                {
                    if (MaxResults > 0 && MaxResults <= 1000) { _maxResults = MaxResults.Value; } 
                }

                return QueryNodes(
                    StartAtPathLocator,
                    _includeSubfolders,
                    _includeData,
                    Name,
                    null,               // Lightswitch can't handle a list of strings...
                    Containing,
                    _maxResults).AsQueryable();

            }

        }

        protected override int Count<T>(IQueryable<T> query)
        {
            return query.Count();
        }


        #endregion

        #region Insert / Update / Delete

        #region helper methods for insert and update

        /// <summary>
        /// Helper method to create a new path locator in order to create a FileNode within the FileTable
        /// hierarchy, optionally underneath a specific existing path.
        /// </summary>
        /// <param name="parentPathLocator">The PathLocator of a the FileNode that should be the parent of the
        /// PathLocator value created by this function.  By default, the PathLocator value will be
        /// created at the root of the FileTable.</param>
        /// <returns></returns>
        private string GetNewPathLocator(string parentPathLocator)
        {
            string PathSeparatorCharacter = "/";
            Guid thisID = Guid.NewGuid();
            byte[] thisIDBytes = thisID.ToByteArray();
            string idString = thisIDBytes.Aggregate(
                new StringBuilder(32), (sb, b) => sb.Append(b.ToString("X2"))).ToString();
            string str1 = Int64.Parse(idString.Substring(0, 12), System.Globalization.NumberStyles.HexNumber).ToString();
            string str2 = Int64.Parse(idString.Substring(12, 12), System.Globalization.NumberStyles.HexNumber).ToString();
            string str3 = Int64.Parse(idString.Substring(24, 8), System.Globalization.NumberStyles.HexNumber).ToString();
            string pathLocator = parentPathLocator;
            if (pathLocator == null || pathLocator.Length == 0) { pathLocator = PathSeparatorCharacter; }
            pathLocator += str1 + "." + str2 + "." + str3 + PathSeparatorCharacter;
            return pathLocator;
        }

        /// <summary>
        /// Helper method to modify a path locator in order to move an existing FileNode underneath another
        /// existing FileNode in the FileTable.
        /// </summary>
        /// <param name="oldPathLocator">The PathLocator of the FileNode to be moved</param>
        /// <param name="newParentPathLocator">The PathLocator of the FileNode's desired parent node</param>
        /// <returns></returns>
        private string UpdatePathLocator (string oldPathLocator, string newParentPathLocator)
        {
            System.Diagnostics.Debug.WriteLine("Detected FileNode move. oldPathLocator = '"
                + oldPathLocator + "', newParentPathLocator = '" + newParentPathLocator + "'");
            string updatedPathLocator;
            
            if (newParentPathLocator == null || newParentPathLocator.Length == 0)
            {
                // the new intended location is root; just change it entirely to move it.
                System.Diagnostics.Debug.WriteLine("Detected that FileNode is moving to the root of the hierarchy");
                updatedPathLocator = GetNewPathLocator(newParentPathLocator); 
            }
            else
            {
                SqlHierarchyId thisId = SqlHierarchyId.Parse(oldPathLocator);
                if (thisId.GetLevel() == 1)
                {
                    // the old location of the FileNode is root; again, just change it entirely to move it
                    System.Diagnostics.Debug.WriteLine("Detected that FileNode is moving from the root of the hierarchy");
                    updatedPathLocator = GetNewPathLocator(newParentPathLocator);
                }
                else
                {
                    // neither the old location nor the new location is root.
                    SqlHierarchyId oldParent = thisId.GetAncestor(1);
                    SqlHierarchyId newParent = SqlHierarchyId.Parse(newParentPathLocator);
                    System.Diagnostics.Debug.WriteLine("Detected that FileNode is moving from and to non-root locations");
                    updatedPathLocator = thisId.GetReparentedValue(oldParent, newParent).ToString();
                }
            }

            System.Diagnostics.Debug.WriteLine("Updating the FileNode's path locator to: " + updatedPathLocator);
            return updatedPathLocator;

        }

        /// <summary>
        /// Tests two PathLocator values and returns true if the currentPathLocator is a direct
        /// descendant of the parentPathLocator, or false if it is not a direct descendant (indicating
        /// that, if the node is being updated, the PathLocator value needs to be recalculated).
        /// </summary>
        /// <param name="currentPathLocator">The current value of PathLocator</param>
        /// <param name="parentPathLocator">The proposed value of ParentPathLocator (may be null)</param>
        /// <returns></returns>
        private bool IsDirectDescendant(string currentPathLocator, string parentPathLocator)
        {
            SqlHierarchyId thisId = SqlHierarchyId.Parse(currentPathLocator);
            if (thisId.GetLevel() == 1 && (parentPathLocator == null || parentPathLocator.Length == 0))
            {
                // it's a first level node in the hierarchy, and parent is null / root
                return true;
            }
            else
            {
                // go up one level, and see if the results compare as the same
                SqlHierarchyId testId = thisId.GetAncestor(1);
                SqlHierarchyId parentId = SqlHierarchyId.Parse(parentPathLocator);
                return (testId.CompareTo(parentId) == 0);
            }

        }

        #endregion

        /// <summary>
        /// Inserts a new FileNode (representing a File or a Folder in an SQL Server FileTable)
        /// </summary>
        /// <param name="theNode">The FileNode to insert</param>
        /// <remarks>On Insert, the following properties are ignored:
        /// - Path (because this is dependent upon the PathLocator value)
        /// - PathLocator (because this cannot be set directly)
        /// - FileType (which is set by the database, based on name)
        /// - Size (which is set by the database, based on file data)
        /// 
        /// To create a file or folder underneath a specific existing folder, set the 
        /// ParentPathLocator property on the newly-inserted node to the PathLocator of the 
        /// desired parent folder.
        /// 
        /// If you want to immediately retrieve the newly-inserted node in order to update
        /// a user interface with automatically-calculated properties, set the ID property
        /// before calling this method, and then use GetNodeByID query to retrieve the newly-
        /// inserted FileNode.
        /// 
        /// If IsFolder is true, any data is ignored (the FileNode will be treated as a folder).
        /// 
        /// </remarks>
        public void InsertFileNode(FileNode theNode)
        {
            // do a fat lot of NOTHING, unless there is at least a node with a Name set
            if (theNode != null && 
                (theNode.Name != null && theNode.Name.Length > 0))
            {
                // figure out up front if we're inserting data or not.
                bool _insertingData = (theNode.IsFolder == false && theNode.Data != null);

                // here again, going old school on the problem.
                // this method writes a T-SQL insert statement and executes it directly.
                StringBuilder strSql = new StringBuilder("INSERT INTO ");
                strSql.Append(_fileTableName);

                // here are the fields we're inserting:
                strSql.Append("(path_locator, stream_id, Name, is_directory");

                // we may or may not insert file data.  
                // if a) it's not a Folder, and b) it has actual data, then we'll insert it.
                if (_insertingData) { strSql.Append(", file_stream"); }

                // that's it.  everything else is set automatically by the database upon insert.
                strSql.Append(") VALUES (@path_locator, @stream_id, @Name, @is_directory");
                if (_insertingData) { strSql.Append(", @file_stream"); }
                strSql.Append(");");

                // build a command and fill in the parameters
                SqlCommand cmd = new SqlCommand(strSql.ToString(), _conn);
                SqlParameter path_locator, stream_id, Name, is_directory;

                // set the PathLocator (see GetNewPathLocator method)
                path_locator = new SqlParameter("@path_locator", GetNewPathLocator(theNode.ParentPathLocator));
                cmd.Parameters.Add(path_locator);
                
                // if given a stream_id, we'll use it; if not, we'll create one and use it
                if (theNode.ID == null || theNode.ID == Guid.Empty) {theNode.ID = Guid.NewGuid(); }
                stream_id = new SqlParameter("@stream_id", theNode.ID);
                cmd.Parameters.Add(stream_id);

                // set the Name
                Name = new SqlParameter("@Name", Safe(theNode.Name));
                cmd.Parameters.Add(Name);

                // set is_directory
                is_directory = new SqlParameter("@is_directory", theNode.IsFolder);
                cmd.Parameters.Add(is_directory);

                // if inserting data, create and add a data parameter
                if (_insertingData)
                {
                    SqlParameter data = new SqlParameter("@file_stream", theNode.Data);
                    cmd.Parameters.Add(data);
                }

                // add to batch of pending operations
                ChangeCommands.Add(cmd);

            }

        }

        /// <summary>
        /// Updates a FileNode to reflect changes to files or folders in an SQL Server FileTable.
        /// </summary>
        /// <param name="theNode">The FileNode to update, in its updated state</param>
        /// <remarks>This method can only change:
        /// - The Name of the FileNode (can also change the FileType)
        /// - The Data in the FileNode (for Files only)
        /// - The PathLocator of the FileNode (to move it to a new location)
        ///         
        /// To create a file or folder underneath a specific existing folder, set the 
        /// ParentPathLocator property on the updated FileNode to the PathLocator of the 
        /// desired parent folder.  This method will move the FileNode in the database.
        /// 
        /// If you want to immediately retrieve the newly-inserted node in order to update
        /// a user interface with automatically-calculated properties, call the 
        /// GetNodeByID query immediately after update to retrieve the newly-updated FileNode.
        /// 
        /// </remarks>
        public void UpdateFileNode(FileNode theNode)
        {
             
            // find the entry in the changeset corresponding to this node
            ChangeSetEntry thisEntry =
                (from e in ChangeSet.ChangeSetEntries
                 where e.Entity == theNode
                 select e).First();
            
            // get the original version of this FileNode that the client saw
            FileNode origNode = (FileNode)thisEntry.OriginalEntity;
            
            // stupid extra trip to the database to get the original PathLocator
            origNode.PathLocator = QueryNodes(
                    null, true, false, null,
                    new List<string> { theNode.ID.ToString() })[0].PathLocator;

            // for *some stupid reason* - the origNode seems to NEVER be right
            // with regard to ParentPathLocator - so, go get that specific 
            // property (yes - extra trip to the database) and update it.
            // Construct an update command that reflects ONLY the changes made
            // by this client to this entity, and nothing else.
            // Note - this is necessary because it is possible for the entity
            // as presented to be modified multiple times in different ways in
            // the same ChangeSet, and if this happens, we need to deal with the
            // complexity that results by modifying each property that changed 
            // individually. 
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _conn;
            StringBuilder strSql = new StringBuilder("UPDATE " 
                + _fileTableName
                + " SET ");

            // Added in 1.0.4:
            // track what we changed -- in case we change *nothing* that is supported
            // (example - trying to change IsFolder after creation)
            bool changedName = false;
            bool changedData = false;
            bool changedPath = false;

            // We will only track three things:
            // 1. Changes to file and folder names
            if (theNode.Name != origNode.Name)
            {
                strSql.Append("name = @name");
                SqlParameter name = new SqlParameter("@name", Safe(theNode.Name));
                cmd.Parameters.Add(name);
                changedName = true; // added in 1.0.4
            }

            // 2. Changes to data, other than wiping out all data within a file
            //    (if you want to do that, do it in the File System, overwrite it
            //    with at least one byte, or just delete the file)
            if ((theNode.Data != origNode.Data) && (theNode.Data != null))
            {
                if (cmd.Parameters.Count > 0) { strSql.Append(", "); }
                strSql.Append("file_stream = @file_stream");
                SqlParameter data = new SqlParameter("@file_stream", theNode.Data);
                cmd.Parameters.Add(data);
                changedData = true;  // added in 1.0.4
            }

            
            // 3. Changes to location (made by changing the ParentPathLocator)
            if (theNode.ParentPathLocator != origNode.ParentPathLocator)
            {
                if (cmd.Parameters.Count > 0) { strSql.Append(", "); }
                strSql.Append("path_locator = @path_locator");
                SqlParameter path_locator = new SqlParameter("@path_locator", 
                    UpdatePathLocator(origNode.PathLocator, theNode.ParentPathLocator));
                cmd.Parameters.Add(path_locator);
                changedPath = true; // added in 1.0.4
            }

            // set the stream_id to indicate which FileNode to change 
            strSql.Append(" WHERE stream_id = @stream_id;");
            SqlParameter stream_id = new SqlParameter("@stream_id", theNode.ID);
            cmd.Parameters.Add(stream_id);

            // Command text is now completed
            cmd.CommandText = strSql.ToString();

            // Added in 1.0.4: 
            // Check whether anything changed.  If not - do nothing (fail silently).
            if (changedName || changedData || changedPath)
            {
                // Add SQL command to the list of commands to be persisted later
                ChangeCommands.Add(cmd);
            }
            else
            {
                // spurious update detected.  reject the change.
                thisEntry.Operation = DomainOperation.None;
            }

        }

        /// <summary>
        /// Deletes a FileNode from the FileTable.
        /// </summary>
        /// <param name="theNode">The FileNode to be deleted</param>
        /// <remarks>
        /// Deletion is not an easily-recoverable action.  Recovering a deleted File or Folder means
        /// getting the File or Folder from a backup and re-inserting it into the FileTable.
        /// 
        /// For this operation, stream_id is required; all other fields are ignored.
        /// </remarks>
        public void DeleteFileNode(FileNode theNode)
        {
            // do a fat lot of NOTHING if we have no stream_id to delete
            if (theNode != null && theNode.ID != null && theNode.ID != Guid.Empty)
            {
                StringBuilder strSql = new StringBuilder("DELETE FROM ");
                strSql.Append(_fileTableName + " ");
                strSql.Append("WHERE stream_id = @stream_id;");

                SqlCommand cmd = new SqlCommand(strSql.ToString(), _conn);
                SqlParameter stream_id;

                // set the stream_id
                stream_id = new SqlParameter("@stream_id", theNode.ID);
                cmd.Parameters.Add(stream_id);

                // Add SQL command to the list of commands to be persisted later
                ChangeCommands.Add(cmd);

            }

        }

        #endregion

    }
        

}